<?php

class Subscribermodel extends MX_Controller {

    function Subscribermodel() {
        // Call the Model constructor
        parent::__construct();
    }

    /* ===== Methods for Event 
      =================================================================== */
    
	function get_table() {
		$table = "Member";

		return $table;
	}
	
    function search_Subscribers($options, $limit) {
        $sql = "
				 			  SELECT *
							  FROM
							  (
			
								SELECT ROW_NUMBER() OVER (ORDER BY Subscriber_id DESC) as NroFila, *
								FROM Subscribers 
								WHERE (
									 First_name LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%' 
									 OR Last_name LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%'
									 OR Email LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%' 
									)
								AND Verified = " . $this->db->escape($options['verified']) . "
								AND Status = " . $this->db->escape($options['status']) . "	
								AND List_type = " . $this->db->escape($options['type']) . "
								";

        $sql .= " ) AS PAG_TABLEXXX

							  WHERE
							  NroFila > " . $limit['offset'] . " AND   NroFila <= " . ($limit['limit']) . "";



        $result = $this->db->query($sql);

        return $result;
    }

    function getcountsearch_Subscribers($options) {


        $sql = "SELECT ROW_NUMBER() OVER (ORDER BY Subscriber_id DESC) as NroFila, *
								FROM Subscribers 
								WHERE (
									 First_name LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%' 
									 OR Last_name LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%'
									 OR Email LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%' 
									)
								AND Verified = " . $this->db->escape($options['verified']) . "
								AND Status = " . $this->db->escape($options['status']) . "
								AND List_type = " . $this->db->escape($options['type']) . "			 
								";

        $result = $this->db->query($sql);

        return $result->num_rows();
    }

    function getsubsriber($subscriberid) {

        $sql = "SELECT  *
							FROM Subscribers 
							WHERE subscriber_id = " . $this->db->escape($subscriberid) . "";

        $result = $this->db->query($sql);

        if ($result->num_rows() > 0) {
            return $result->row();
        } else {
            return false;
        }
    }

    function addsubscriber($data) {
        $sql = "INSERT INTO Subscribers (First_name, Last_name, Email, Status, Verified, Verification_code, Date_code_generated, Date_verified, List_type, MarketFID) VALUES (
                    " . $this->db->escape($data['first_name']) . ",
                    " . $this->db->escape($data['last_name']) . ",
                    " . $this->db->escape($data['email']) . ",
                    " . $this->db->escape($data['status']) . ",
                    " . $this->db->escape($data['verified']) . ",
                    " . $this->db->escape($data['verification_code']) . ",
                    " . $this->db->escape($data['date_code_generated']) . ",  
                    " . $this->db->escape($data['date_verified']) . ",
                    " . $this->db->escape($data['list_type']) . ",
                    " . $this->db->escape($data['market']) . "
            )";

        $this->db->query($sql);
    }

    function checkexists($data) {
        $sql = "SELECT * FROM Subscribers WHERE Email = " . $this->db->escape($data['email']) . " AND Status = '1' AND List_type = " . $this->db->escape($data['list_type']) . "";

        $result = $this->db->query($sql);

        if ($result->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    function update_subscriber($data) {
        $sql = "UPDATE Subscribers SET 
                        First_name = " . $this->db->escape($data['first_name']) . ",
                        Last_name = " . $this->db->escape($data['last_name']) . ",
                        Email = " . $this->db->escape($data['email']) . ",
                        Status = " . $this->db->escape($data['status']) . ",
                        Verified = " . $this->db->escape($data['verified']) . " ,
                        Verification_code = " . $this->db->escape($data['verification_code']) . " ,
                        Date_code_generated = " . $this->db->escape($data['date_code_generated']) . " ,
                        Date_verified = " . $this->db->escape($data['date_verified']) . " ,
                        List_type = " . $this->db->escape($data['list_type']) . " 
                    WHERE Subscriber_id = " . $this->db->escape($data['subscriber_id']) . "";
        $this->db->query($sql);
    }

    function change_verification_subscriber($verification_code, $status) {

        if ($status == '1') {
            $sql = "UPDATE Subscribers SET 
					Verified = " . $this->db->escape($status) . " , 
					Date_verified = GETDATE()
					WHERE Verification_code = " . $this->db->escape($verification_code) . "  
			";
        } else if ($status == '0') {
            $sql = "UPDATE Subscribers SET 
					Verified = " . $this->db->escape($status) . "  
					WHERE Verification_code = " . $this->db->escape($verification_code) . "  
			";
        }

        $this->db->query($sql);


        //delete verified records
        $sql2 = "Delete FROM Subscribers WHERE verified = 0
				AND datediff(hh, date_code_generated, cast(CURRENT_TIMESTAMP as datetime))>24";

        $this->db->query($sql2);
    }

    function check_verification_code($verification_code) {
        $sql = "SELECT *
				FROM Subscribers 
				WHERE Verification_code = " . $this->db->escape($data['verification_code']) . "			 
				";

        $result = $this->db->query($sql);

        if ($result->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    function getby_verification_code($verification_code) {
        $sql = "SELECT * FROM Subscribers WHERE Verification_code = " . $this->db->escape($verification_code) . "";

        $result = $this->db->query($sql);

        if ($result->num_rows() > 0) {
            return $result->row();
        } else {
            return false;
        }
    }

    function changestatus_subscriber($subscriberid, $status) {

        $idList = implode(',', $subscriberid);

        $sql = "UPDATE Subscribers SET Status = " . $this->db->escape($status) . " 
                        WHERE Subscriber_id in ( " . $idList . ")
		";

        $this->db->query($sql);
    }

    function get_Subscribers_for_export($options, $limit) {
        $sql = " SELECT *
                    FROM
                    (

                          SELECT ROW_NUMBER() OVER (ORDER BY Subscriber_id DESC) as [order], Subscriber_id, First_name, Last_name, Email, Date_verified
                          FROM Subscribers 
                          WHERE (
                                   First_name LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%' 
                                   OR Last_name LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%'
                                   OR Email LIKE '%" . $this->db->escape_like_str($options['keyword']) . "%' 
                                  )
                          AND Verified = " . $this->db->escape($options['verified']) . "
                          AND Status = " . $this->db->escape($options['status']) . "	
                          AND List_type = " . $this->db->escape($options['type']) . "		 
                          ";

        $sql .= " ) AS PAG_TABLEXXX
                        WHERE [order] > " . $limit['offset'] . " AND [order] <= " . ($limit['limit']) . "";

        $result = $this->db->query($sql);
        return $result;
    }
    
    

}

?>